# # -*- coding:utf8 -*- #
# # -----------------------------------------------------------------------------------
# # ProjectName:  smart-meter-iot-test
# # FileName:     connect_Mysql.py
# # Author  :     
# # Datetime:     2022/8/16 15:18
# # Description：
# # -----------------------------------------------------------------------------------
# import sshtunnel
# from pymysql import Connect
# from sshtunnel import SSHTunnelForwarder
# from common.util.configUtil.configUtil import ConfigUtil
# from common.util.logUtil.logUtil import LoggerUtil
#
# logger = LoggerUtil()
#
#
# class MySqlUtil:
#     def __init__(self):
#         self.connect = None
#
#     def db_connect(self):
#         try:
#             config = ConfigUtil()
#             dataBaseEnvironment = config.get_database_status()
#             if dataBaseEnvironment is True:
#                 SSH_Proxy_host = config.get_database_info("SSH_Proxy_host")
#                 SSH_Proxy_port = int(config.get_database_info("SSH_Proxy_port"))  # 端口号需要是整型
#                 SSH_Proxy_user = config.get_database_info("SSH_Proxy_user")
#                 SSH_Proxy_password = config.get_database_info("SSH_Proxy_password")
#                 Pro_host = config.get_database_info("Pro_host")
#                 Pro_port = int(config.get_database_info("Pro_port"))  # 端口号需要是整型
#                 Pro_user = config.get_database_info("Pro_user")
#                 Pro_password = config.get_database_info("Pro_password")
#                 Pro_database = config.get_database_info("Pro_database")
#                 server = SSHTunnelForwarder(
#                     (SSH_Proxy_host, SSH_Proxy_port),  # 这里写入B 跳板机IP、端口
#                     ssh_username=SSH_Proxy_user,  # 跳板机 用户名
#                     ssh_password=SSH_Proxy_password,  # 跳板机 密码
#                     remote_bind_address=(Pro_host, Pro_port),  # 这里写入 C数据库的 IP、端口号
#                 )
#                 server.start()
#                 self.connect = Connect(
#                     host="127.0.0.1",  # 只能写 127.0.0.1，这是固定的，不可更改
#                     port=server.local_bind_port,  # 固定可不用更改
#                     user=Pro_user,  # 数据库 用户名
#                     password=Pro_password,  # 数据库 密码
#                     db=Pro_database,  # 填写需要连接的数据库名
#                     charset='utf8', connect_timeout=100, read_timeout=500
#                 )
#                 cursor = self.connect.cursor()
#                 return cursor
#             else:
#                 Test_user = config.get_database_info("Test_user")
#                 Test_password = config.get_database_info("Test_password")
#                 Test_host = config.get_database_info("Test_host")
#                 Test_port = int(config.get_database_info("Test_port"))  # 端口号需要是整型
#                 Test_database = config.get_database_info("Test_database")
#                 self.connect = Connect(user=Test_user, password=Test_password, host=Test_host, port=Test_port,
#                                        database=Test_database, connect_timeout=100, read_timeout=500)
#                 cursor = self.connect.cursor()  # 建立游标，通过游标执行SQL语句
#                 return cursor
#         except sshtunnel.HandlerSSHTunnelForwarderError as handlerError:
#             self.db_connect()
#             logger.error(handlerError)
#         except sshtunnel.BaseSSHTunnelForwarderError as baseError:
#             print(baseError)
#             logger.error(baseError)
#         except Exception as error:
#             print(error)
#             logger.error(error)
#
#     def close_connect(self):
#         self.connect.close()
#
#
# connectdb = MySqlUtil()
#
#
# def select_sql(sql):
#     try:
#         cursor = connectdb.db_connect()
#         cursor.execute(sql)
#         result = cursor.fetchall()
#         allResult = []
#         for i in result:
#             row_list = []
#             for k in i:
#                 if k is "" or k is None:
#                     k = 0
#                 row_list.append(k)
#             allResult.append(row_list)
#         cursor.close()
#         connectdb.close_connect()
#         if len(allResult) == 1:
#             if len(allResult[0]) == 1:  # 判断sql查询是否多个字段
#                 return allResult[0][0]
#             else:
#                 return allResult
#         else:
#             return allResult
#     except Exception as e:
#         connectdb.close_connect()
#         select_sql(sql)
#         logger.error(e)
#
#
# if __name__ == '__main__':
#     cd = MySqlUtil()
#     cd.db_connect()
